﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Threading.Tasks;


namespace F3.Maps.OsmParser
{
    class SpatialightDataAdapter 
    {
        public DataTable dataTable;
        private static int id = 0;
        private string connectionString;
        //= @"Data Source= E:\Projects\Maps\OsmParser\OsmParser\bin\Debug\Scheme3.sqlite";
        //private string connectionString = ;
        string tableName;
        SQLiteConnection cn;
        
        private SpatialightDataAdapter()
        {
            dataTable = new DataTable();
            dataTable.Columns.Add("ID");
            dataTable.Columns.Add("OsmID");
            dataTable.Columns.Add("Name");
            dataTable.Columns.Add("Highway");
            dataTable.Columns.Add("Oneway");
            dataTable.Columns.Add("startnode");
            dataTable.Columns.Add("endNode");
            dataTable.Columns.Add("distance");
            dataTable.Columns.Add("Geometry");

            //connectionString = Properties.Settings.Default.database;
            //tableName = Properties.Settings.Default.roadsTable;
            ////tableName = "routableroads";           
                        
        }

        public SpatialightDataAdapter(string connectionString, string tableName)
        {
            this.connectionString = "Data Source= " + connectionString;
            this.tableName = tableName;

        }

        

        public void Begin()
        { 
            cn = new SQLiteConnection(connectionString);
            cn.Open();
            SQLiteCommand cm = new SQLiteCommand(String.Format("SELECT load_extension('{0}');", "libspatialite-2.dll"), cn);
            cm.ExecuteNonQuery();
            
            try
            {
                SQLiteCommand creatTable = new SQLiteCommand("CREATE TABLE " + this.tableName + " (" +
                                                   "id INTEGER NOT NULL PRIMARY KEY autoincrement" +
                                                   ",osmid TEXT" +
                                                   ",name TEXT" +
                                                   ",highway TEXT" +
                                                   ",fromto INTEGER" + //Boolean
                                                   ",tofrom INTEGER" + //Boolean
                                                   ",maxspeed TEXT" +
                                                   ",f_node INTEGER" +
                                                   ",t_node INTEGER" +
                                                   ",distance REAL" +
                                                   ")"
                                                   , cn);
                SQLiteCommand addGeometryColumn = new SQLiteCommand("SELECT AddGeometryColumn('" + tableName + "', 'Geometry', 4326, 'LINESTRING', 2)", cn);
                
                creatTable.ExecuteNonQuery();
                addGeometryColumn.ExecuteNonQuery();
            }
            catch
            {
                SQLiteCommand deleteRecords = new SQLiteCommand("delete from " + this.tableName, cn);
                deleteRecords.ExecuteNonQuery();
            }

            SQLiteCommand begin = new SQLiteCommand("Begin", cn);
            begin.ExecuteNonQuery();
            
        }

        public void End()
        {
            SQLiteCommand end = new SQLiteCommand("End", cn);
            end.ExecuteNonQuery();
            cn.Close();
        }


        private void Add(HighwayNode highway, string geometry, string startNodeID, string endNodeID, double distance)
        {
            
            
            string command = "INSERT INTO " + this.tableName +
                             "(osmid, name, highway, fromto, tofrom, maxspeed, f_node, t_node, distance, Geometry) VALUES (" +
                             "'" + highway.ID + "'" +
                             ",'" + highway.Name.Replace("'", "''") + "'" +
                             ",'" + highway.HighwayType + "'" +
                             ",1" + 
                             ","  + (highway.Oneway == string.Empty ? "1" : "0")  +
                             ",'" + highway.MaxSpeed + "'" +
                             "," + startNodeID  +
                             "," + endNodeID +
                             "," + distance +
                             ", geomfromtext('" + geometry + "', 4326))";
            
            SQLiteCommand insert = new SQLiteCommand(command, cn);
            insert.ExecuteNonQuery();
                    
        }

        public void Save(HighwayNode highway, string geometry, string startNodeID, string endNodeID, double distance)
        {
            
            //Task.Factory.StartNew(() =>
            //            {
            //                this.dataTable.Rows.Add(++id, highway.ID, highway.Name, highway.HighwayType, highway.Oneway, startNodeID, endNodeID, distance, geometry);
            //                this.Add(highway, geometry, startNodeID, endNodeID, distance);
            //            });


            //this.dataTable.Rows.Add(++id, highway.ID, highway.Name, highway.HighwayType, highway.Oneway, startNodeID, endNodeID, distance, geometry);
            this.Add(highway, geometry, startNodeID, endNodeID, distance);
        }

        
    }
}
